package com.geek688.auxiliarytool.util;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.swing.JTextArea;

import com.geek688.auxiliarytool.bean.FreeMarkBean;
import com.geek688.auxiliarytool.bean.Log;
import com.geek688.auxiliarytool.util.sqlite.RowMapper;
import com.geek688.auxiliarytool.util.sqlite.SqliteHelper;


import lombok.extern.slf4j.Slf4j;

@Slf4j
public class LogUtil {

	// 日志文件输出路径
	private static String logPath = "D:/auxiliarytool/log.db";
	static SqliteHelper helper = null;
	@SuppressWarnings("unused")
	private static JTextArea area = null;

	public LogUtil(JTextArea area) {
		LogUtil.area = area;
	}

	public LogUtil() {
		
	}
	/**
	 * @Title createLogDB
	 * @description 创建操作日志表
	 * @author geek
	 * return void
	 */
	public static void createLogDB() {
		try {
			File file = new File(logPath);
			if (!file.exists()) {
				log.debug("文件不存在！");
				helper = new SqliteHelper(logPath);
				int r = helper.executeUpdate("CREATE TABLE `log`(`id` INTEGER PRIMARY KEY AUTOINCREMENT,`dbname` varchar(150),`username` varchar(150),`password` varchar(50),`url` varchar(255),`port` varchar(30),`templatePath` varchar(255),`projectPath` varchar(255),`created` timestamp );");
				int rr = helper.executeUpdate("CREATE TABLE `package`(`id` INTEGER PRIMARY KEY AUTOINCREMENT,`packageName` varchar(255),`created` timestamp );");
				int rrr = helper.executeUpdate("CREATE TABLE `freemark` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `key` varchar(255) , `value` varchar(255), `description` varchar(255), `created` varchar(100));");
				int rrrr = helper.executeUpdate("CREATE TABLE `template`(`id` INTEGER PRIMARY KEY AUTOINCREMENT,`template` varchar(255),`created` timestamp );");
				if (r > 0 && rr > 0 && rrr >0 && rrrr>0) {
					log.debug("建表成功！");
				}else{
					log.debug("建表失败！");
				}
			}
//			FreeMarkBean bean = null;
//			helper = new SqliteHelper(logPath);
//			bean = helper.executeQuery("SELECT * FROM freemark WHERE `key` ='author' AND `value`='作者'",new ResultSetExtractor<FreeMarkBean>() {
//				@Override
//				public FreeMarkBean extractData(ResultSet rs) {
//					FreeMarkBean markBean = new FreeMarkBean();
//					try {
//						markBean.setKey(rs.getString("key"));
//						markBean.setValue(rs.getString("value"));
//						markBean.setDescription(rs.getString("description"));
//					} catch (Exception e) {
//						log.error("读取操作日志异常", e);
//					}
//					return markBean;
//				}
//			});
//			if (bean == null) {
//				writeFreeMarkSet("author", "作者", "");
//			}
		} catch (ClassNotFoundException e) {
			log.error("建表失败",e);
		} catch (SQLException e) {
			log.error("建表失败",e);
		}
	}
	/**
	 * @Title readLog
	 * @description 读取操作日志
	 * @author geek
	 * return Log
	 */
	public static Log readLog() {
		createLogDB();
		Log logDb = null;
		try {
			helper = new SqliteHelper(logPath);
			List<Log> logs = helper.executeQuery("SELECT * FROM log WHERE id IN(SELECT MAX(id) FROM log)", new RowMapper<Log>() {
				@Override
				public Log mapRow(ResultSet rs, int index) throws SQLException {
					Log log = new Log();
					log.setDbname(rs.getString("dbname"));
					log.setUsername(rs.getString("username"));
					log.setPassword(rs.getString("password"));
					log.setUrl(rs.getString("url"));
					log.setPort(rs.getString("port"));
//					log.setTemplatePath(rs.getString("templatePath"));
					log.setProjectPath(rs.getString("projectPath"));
					return log;
				}
			});
			List<String> templatePaths = helper.executeQuery("SELECT distinct template FROM template where template is not null order by created DESC", new RowMapper<String>() {
				@Override
				public String mapRow(ResultSet rs, int index) throws SQLException {
					return rs.getString("template");
				}
			});
			List<String> packageNames = helper.executeQuery("SELECT distinct packageName FROM package where packageName is not null order by created DESC", new RowMapper<String>() {
				@Override
				public String mapRow(ResultSet rs, int index) throws SQLException {
					return rs.getString("packageName");
				}
			});
			if ( logs!= null && logs.size()>=1) {
				logDb = logs.get(0);
				LogUtil.clearLog();
				logDb.getPackageName().addAll(packageNames);
				logDb.getTemplatePath().addAll(templatePaths);
			}
		} catch (Exception e) {
			log.error("读取操作日志异常", e);
		}
		//为空时，返回一个实例化对象
		if (logDb == null) {
			logDb = new Log();
		}
		return logDb;
	}
	/**
	 * @Title: readFreeMarkSet
	 * @Description: 自定义参数读取
	 * @author geek
	 * @return List<FreeMarkBean>
	 */
	public static List<FreeMarkBean> readFreeMarkSet() {
		List<FreeMarkBean> logs = new ArrayList<FreeMarkBean>();
		try {
			helper = new SqliteHelper(logPath);
			logs = helper.executeQuery("SELECT * FROM freemark ", new RowMapper<FreeMarkBean>() {
				@Override
				public FreeMarkBean mapRow(ResultSet rs, int index) throws SQLException {
					FreeMarkBean markBean = new FreeMarkBean();
					markBean.setKey(rs.getString("key"));
					markBean.setValue(rs.getString("value"));
					markBean.setDescription(rs.getString("description"));
					return markBean;
				}
			});
		} catch (Exception e) {
			log.error("读取操作日志异常", e);
		}
		return logs;
	}
	/**
	 * @Title: writeFreeMarkSet
	 * @Description: 自定义参数写入
	 * @author geek
	 * @param key
	 * @param value
	 * @param description
	 * @return void
	 */
	public static void writeFreeMarkSet(String key,String value,String description) {
		StringBuffer sql = new StringBuffer("INSERT INTO freemark (key,value,description,created) values(");
		sql.append("'"+key.concat("',"));
		sql.append("'"+value.concat("',"));
		sql.append("'"+description.concat("',"));
		sql.append("'"+DateUtils.dateToStr(new Date()).concat("');"));
		log.debug("write freemark : "+sql.toString());
		try {
			helper = new SqliteHelper(logPath);
			int result = helper.executeUpdate(sql.toString());
			if (result<0) {
				log.error("自定义参数写入失败");
			}
		} catch (ClassNotFoundException e) {
			log.error("自定义参数写入失败",e);
		} catch (SQLException e) {
			log.error("自定义参数写入失败",e);
		}
	}
	/**
	 * @Title writeLog
	 * @description 写入操作日志
	 * @author geek
	 * @param logDb
	 * return void
	 */
	public static void writeLog(Log logDb) {
		StringBuffer sql = new StringBuffer("INSERT INTO log (dbname,url,port,username,password,projectPath,created) values(");
		sql.append("'"+logDb.getDbname()+"',");
		sql.append("'"+logDb.getUrl()+"',");
		sql.append("'"+logDb.getPort()+"',");
		sql.append("'"+logDb.getUsername()+"',");
		sql.append("'"+logDb.getPassword()+"',");
		sql.append("'"+logDb.getProjectPath()+"',");
		sql.append("'"+DateUtils.dateToStr(new Date()).concat("');"));
		log.debug("write logDb : "+sql.toString());
		try {
			helper = new SqliteHelper(logPath);
			int result = helper.executeUpdate(sql.toString());
			if (result<0) {
				log.error("日志信息写入失败");
			}
		} catch (ClassNotFoundException e) {
			log.error("日志信息写入失败",e);
		} catch (SQLException e) {
			log.error("日志信息写入失败",e);
		} 
	}
	/**
	 * @Title writeLog
	 * @description 写入操作日志
	 * @author geek
	 * @param name 字段名
	 * return void
	 */
	public static void writeLog(String name) {
		StringBuffer sql = new StringBuffer("INSERT INTO package (packageName,created) values(");
		sql.append("'"+name.concat("',"));
		sql.append("'"+DateUtils.dateToStr(new Date()).concat("');"));
		log.debug("write package : "+sql.toString());
		try {
			helper = new SqliteHelper(logPath);
			int result = helper.executeUpdate(sql.toString());
			if (result<0) {
				log.error("日志信息写入失败");
			}
		} catch (ClassNotFoundException e) {
			log.error("日志信息写入失败",e);
		} catch (SQLException e) {
			log.error("日志信息写入失败",e);
		}
	}
	public static void writeTemplateLog(String name) {
		StringBuffer sql = new StringBuffer("INSERT INTO template (template,created) values(");
		sql.append("'"+name.concat("',"));
		sql.append("'"+DateUtils.dateToStr(new Date()).concat("');"));
		log.debug("write package : "+sql.toString());
		try {
			helper = new SqliteHelper(logPath);
			int result = helper.executeUpdate(sql.toString());
			if (result<0) {
				log.error("日志信息写入失败");
			}
		} catch (ClassNotFoundException e) {
			log.error("日志信息写入失败",e);
		} catch (SQLException e) {
			log.error("日志信息写入失败",e);
		}
	}
	/**
	 * @Title clearLog
	 * @description 删除表
	 * @author geek
	 * @param tableName 表名
	 * return void
	 */
	public static void clearLog(String tableName) {
		StringBuffer sql = new StringBuffer("DELETE FROM "+tableName);
		log.debug("clear package list : "+sql.toString());
		try {
			helper = new SqliteHelper(logPath);
			int result = helper.executeUpdate(sql.toString());
			if (result<0) {
				log.error("日志信息清空失败");
			}
		} catch (ClassNotFoundException e) {
			log.error("日志信息清空失败",e);
		} catch (SQLException e) {
			log.error("日志信息清空失败",e);
		}
	}
	/**
	 * @Title clearLog
	 * @description 清空日志重复数据
	 * @author geek
	 * return void
	 */
	public static void clearLog() {
		//删除重复数据，只保留一条
		StringBuffer sql = new StringBuffer("DELETE FROM log WHERE id NOT IN ( SELECT dt.minno FROM(SELECT MIN(id) AS minno FROM log GROUP BY dbname,url,projectPath,templatePath) dt)");
		log.debug("clear duplicate data log : "+sql.toString());
		try {
			helper = new SqliteHelper(logPath);
			int result = helper.executeUpdate(sql.toString());
			if (result<0) {
				log.error("日志信息清空失败");
			}
		} catch (ClassNotFoundException e) {
			log.error("日志信息清空失败",e);
		} catch (SQLException e) {
			log.error("日志信息清空失败",e);
		}
	}
}
